﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel;
using System.Data;
using System.Transactions;


using Global.Class.Library;
using DataTransferObject;
using DataAccessLayer;
using DataAccessLayer.GCGBCommandDTSTableAdapters;
using DataAccessLayer.ERmgrVCPTableAdapters;


namespace BusinessLogicLayer
{
    public class GCGBCommandBLL : NotifyPropertyChangeObject
    {
        public GlobalEnum.TaskID TaskID { get { return GlobalEnum.TaskID.GCGBCommand; } }

        private UserInformation userOrganization;

        private GCGBCommandMaster gcgbCommandMaster;

        private BindingList<GCGBCommandCollection> gcgbCommandCollectionList;



        public GCGBCommandBLL()
        {
            try
            {
                if (GlobalVariables.shouldRestoreProcedure) RestoreProcedure();

                userOrganization = new UserInformation();

                gcgbCommandMaster = new GCGBCommandMaster();

                this.gcgbCommandCollectionList = new BindingList<GCGBCommandCollection>();

                GlobalDefaultValue.Apply(this);


                this.GCGBCommandMaster.PropertyChanged += new PropertyChangedEventHandler(GCGBCommandMaster_PropertyChanged);

                this.GCGBCommandCollectionList.ListChanged += new ListChangedEventHandler(GCGBCommandCollection_ListChanged);

            }
            catch (Exception exception)
            {
                throw exception;
            }
        }

        private void GCGBCommandMaster_PropertyChanged(object sender, PropertyChangedEventArgs e)
        {
            this.SetDirty();
        }

        private void GCGBCommandCollection_ListChanged(object sender, ListChangedEventArgs e)
        {
            this.SetDirty();
        }




        #region <Adapter>

        private GCGBCommandListingTableAdapter listingTableAdapter;
        protected GCGBCommandListingTableAdapter ListingTableAdapter
        {
            get
            {
                if (listingTableAdapter == null) listingTableAdapter = new GCGBCommandListingTableAdapter();
                return listingTableAdapter;
            }
        }

        private GCGBCommandMasterTableAdapter masterTableAdapter;
        protected GCGBCommandMasterTableAdapter MasterTableAdapter
        {
            get
            {
                if (masterTableAdapter == null) masterTableAdapter = new GCGBCommandMasterTableAdapter();
                return masterTableAdapter;
            }
        }

        private GCGBCommandCollectionTableAdapter collectionTableAdapter;
        protected GCGBCommandCollectionTableAdapter CollectionTableAdapter
        {
            get
            {
                if (collectionTableAdapter == null) collectionTableAdapter = new GCGBCommandCollectionTableAdapter();
                return collectionTableAdapter;
            }
        }

        private GCGBCommandDetailTableAdapter detailTableAdapter;
        protected GCGBCommandDetailTableAdapter DetailTableAdapter
        {
            get
            {
                if (detailTableAdapter == null) detailTableAdapter = new GCGBCommandDetailTableAdapter();
                return detailTableAdapter;
            }
        }

        private ListMaterialSetDetailTableAdapter materialSetDetailTableAdapter;
        protected ListMaterialSetDetailTableAdapter MaterialSetDetailTableAdapter
        {
            get
            {
                if (materialSetDetailTableAdapter == null) materialSetDetailTableAdapter = new ListMaterialSetDetailTableAdapter();
                return materialSetDetailTableAdapter;
            }
        }

        #endregion <Adapter>

        #region <Storage>

        public UserInformation UserOrganization
        {
            get { return this.userOrganization; }
            set { this.userOrganization = value; }
        }

        public GCGBCommandDTS.GCGBCommandListingDataTable GCGBCommandListing(DateTime lowerFillterDate, DateTime upperFillterDate)
        {
            return this.ListingTableAdapter.GetData(lowerFillterDate, upperFillterDate);
        }

        public GCGBCommandMaster GCGBCommandMaster
        {
            get { return this.gcgbCommandMaster; }
        }

        public BindingList<GCGBCommandCollection> GCGBCommandCollectionList
        {
            get { return this.gcgbCommandCollectionList; }
        }


        #endregion <Storage>

        #region Properties

        #region <Primary Key>

        public int GCGBCommandID   //Primary Key
        {
            get { return this.GCGBCommandMaster.GCGBCommandID; }
            private set
            {
                if (this.GCGBCommandMaster.GCGBCommandID != value)
                {
                    this.StopTracking();

                    this.GCGBCommandMaster.GCGBCommandID = value;

                    this.GCGBCommandGetMaster();
                    this.GCGBCommandGetDetail();

                    this.StartTracking();
                    this.Reset();
                }

            }
        }

        #endregion <Primary Key>

        private void GCGBCommandGetMaster()
        {
            if (this.GCGBCommandID > 0)
            {
                GCGBCommandDTS.GCGBCommandMasterDataTable masterDataTable = this.MasterTableAdapter.GetData(this.GCGBCommandID);

                if (masterDataTable.Count > 0)
                {
                    this.GCGBCommandMaster.StopTracking();

                    this.GCGBCommandMaster.GCGBCommandTypeID = masterDataTable[0].GCGBCommandTypeID;
                    this.GCGBCommandMaster.GCGBCommandDate = masterDataTable[0].GCGBCommandDate;
                    this.GCGBCommandMaster.EntryDate = masterDataTable[0].EntryDate;
                    this.GCGBCommandMaster.GCGBCommandReference = masterDataTable[0].GCGBCommandReference;
                    this.GCGBCommandMaster.Description = masterDataTable[0].Description;
                    this.GCGBCommandMaster.PreparedEmployeeID = masterDataTable[0].PreparedEmployeeID;
                    this.GCGBCommandMaster.ApprovedEmployeeID = masterDataTable[0].ApprovedEmployeeID;
                    this.GCGBCommandMaster.Remarks = masterDataTable[0].Remarks;

                    this.GCGBCommandMaster.StartTracking();

                    this.GCGBCommandMaster.Reset();

                    this.UserOrganization.UserID = masterDataTable[0].UserID;
                    this.UserOrganization.UserOrganizationID = masterDataTable[0].UserOrganizationID;
                }
                else throw new System.ArgumentException("Insufficient get data");
            }
            else
            {
                GlobalDefaultValue.Apply(this.GCGBCommandMaster);
                this.GCGBCommandMaster.GCGBCommandDate = DateTime.Today;
                this.GCGBCommandMaster.EntryDate = DateTime.Today;
                this.GCGBCommandMaster.Reset();
            }
        }


        private void GCGBCommandGetDetail()
        {
            this.gcgbCommandCollectionList.RaiseListChangedEvents = false;
            this.gcgbCommandCollectionList.Clear();
            if (this.GCGBCommandID >= 0)
            {

                GCGBCommandDTS.GCGBCommandCollectionDataTable collectionDataTable = this.CollectionTableAdapter.GetData(this.GCGBCommandID);

                if (collectionDataTable.Count > 0)
                {
                    foreach (GCGBCommandDTS.GCGBCommandCollectionRow collectionRow in collectionDataTable.Rows)
                    {
                        this.gcgbCommandCollectionList.Add(new GCGBCommandCollection(collectionRow.MaterialSetID, collectionRow.Quantity, collectionRow.MaterialSemiID1, collectionRow.QuantityPerSet1, collectionRow.MaterialSemiID2, collectionRow.QuantityPerSet2, collectionRow.MaterialSemiID3, collectionRow.QuantityPerSet3, collectionRow.Remarks));
                    }
                }
            }
            this.gcgbCommandCollectionList.RaiseListChangedEvents = true;
            this.gcgbCommandCollectionList.ResetBindings();
        }

        public void InitializeMaterialSet(GCGBCommandCollection gcgbCommandCollection)
        {
            this.gcgbCommandCollectionList.RaiseListChangedEvents = false;
            
            gcgbCommandCollection.MaterialSemiID1 = 0; gcgbCommandCollection.QuantityPerSet1 = 0;
            gcgbCommandCollection.MaterialSemiID2 = 0; gcgbCommandCollection.QuantityPerSet2 = 0;
            gcgbCommandCollection.MaterialSemiID3 = 0; gcgbCommandCollection.QuantityPerSet3 = 0;
            
            ERmgrVCP.ListMaterialSetDetailDataTable materialSetDetailDataTable = this.MaterialSetDetailTableAdapter.GetData(gcgbCommandCollection.MaterialSetID);
            for (int i = 1; i <= materialSetDetailDataTable.Rows.Count; i++)
            {
                if (i == 1) { gcgbCommandCollection.MaterialSemiID1 = ((ERmgrVCP.ListMaterialSetDetailRow)materialSetDetailDataTable.Rows[i - 1]).MaterialSemiID; gcgbCommandCollection.QuantityPerSet1 = ((ERmgrVCP.ListMaterialSetDetailRow)materialSetDetailDataTable.Rows[i - 1]).MaterialSemiID; }
                if (i == 2) { gcgbCommandCollection.MaterialSemiID2 = ((ERmgrVCP.ListMaterialSetDetailRow)materialSetDetailDataTable.Rows[i - 1]).MaterialSemiID; gcgbCommandCollection.QuantityPerSet2 = ((ERmgrVCP.ListMaterialSetDetailRow)materialSetDetailDataTable.Rows[i - 1]).MaterialSemiID; }
                if (i == 3) { gcgbCommandCollection.MaterialSemiID3 = ((ERmgrVCP.ListMaterialSetDetailRow)materialSetDetailDataTable.Rows[i - 1]).MaterialSemiID; gcgbCommandCollection.QuantityPerSet3 = ((ERmgrVCP.ListMaterialSetDetailRow)materialSetDetailDataTable.Rows[i - 1]).MaterialSemiID; }
            }
            
            this.gcgbCommandCollectionList.RaiseListChangedEvents = true;
            this.gcgbCommandCollectionList.ResetBindings();
        }

        #endregion Properties





        #region Object Permission

        public override bool IsValid
        {
            get
            {
                List<GCGBCommandCollection> inValidGCGBCommandCollection = this.GCGBCommandCollectionList.Where(gcgbCommandCollection => !gcgbCommandCollection.IsValid).ToList();
                return this.GCGBCommandMaster.IsValid && inValidGCGBCommandCollection.Count == 0;
            }
        }

        public bool ReadOnly
        {
            get
            {
                try
                {
                    return GlobalUserPermission.GetUserReadOnly(GlobalVariables.GlobalUserInformation.UserID, this.TaskID);
                }
                catch
                {
                    return true;
                }
            }
        }

        public bool Editable
        {
            get
            {
                try
                {
                    if (this.gcgbCommandMaster.GCGBCommandDate <= GlobalUserPermission.GlobalLockedDate()) return false;

                    if (!GlobalUserPermission.GetUserEditable(GlobalVariables.GlobalUserInformation.UserID, this.TaskID, this.UserOrganization.UserOrganizationID)) return false;

                    if (!GlobalUserPermission.GetEditable("GCGBCommandApproved", this.GCGBCommandID)) return false;

                    return GlobalUserPermission.GetEditable("GCGBCommandEditable", this.GCGBCommandID);
                }
                catch
                {
                    return false;
                }
            }
        }

        public bool Verifiable
        {
            get
            {
                try
                {
                    if (this.gcgbCommandMaster.GCGBCommandDate <= GlobalUserPermission.GlobalLockedDate()) return false;

                    if (!GlobalUserPermission.GetUserEditable(GlobalVariables.GlobalUserInformation.UserID, GlobalEnum.TaskID.GCGBCommandVerifiable, this.UserOrganization.UserOrganizationID)) return false;

                    return GlobalUserPermission.GetEditable("GCGBCommandEditable", this.GCGBCommandID);
                }
                catch
                {
                    return false;
                }
            }
        }

        public bool Unverifiable
        {
            get
            {
                try
                {
                    if (this.gcgbCommandMaster.GCGBCommandDate <= GlobalUserPermission.GlobalLockedDate()) return false;

                    if (!GlobalUserPermission.GetUserEditable(GlobalVariables.GlobalUserInformation.UserID, GlobalEnum.TaskID.GCGBCommandUnverifiable, this.UserOrganization.UserOrganizationID)) return false;

                    return GlobalUserPermission.GetEditable("GCGBCommandEditable", this.GCGBCommandID);
                }
                catch
                {
                    return false;
                }
            }
        }


        #endregion Object Permission






        public void Fill(int gcgbCommandID)
        {
            if (this.GCGBCommandID == gcgbCommandID) this.GCGBCommandID = -1; //Enforce to reload
            this.GCGBCommandID = gcgbCommandID;
        }

        public void New()
        {
            if (this.GCGBCommandID == 0) this.GCGBCommandID = -1;
            this.GCGBCommandID = 0;
        }

        public void Edit()
        {

        }

        #region Save & Delete Method


        public bool Save()
        {
            int gcgbCommandID = 0;

            try
            {

                if (!this.SaveValidate()) throw new System.ArgumentException("Insufficient save", "Save validate");

                using (TransactionScope transactionScope = new TransactionScope())
                {
                    if (!this.Editable) throw new System.ArgumentException("Insufficient save", "Uneditable");

                    if (!this.SaveMaster(ref gcgbCommandID)) throw new System.ArgumentException("Insufficient save", "Save master");

                    if (!this.SaveDetail(gcgbCommandID)) throw new System.ArgumentException("Insufficient save", "Save detail");

                    if (this.SaveConflict()) throw new System.ArgumentException("Insufficient save", "Save conflict");

                    transactionScope.Complete();                    
                }

                this.Fill(gcgbCommandID);
                return true;
            }
            catch (System.Exception exception)
            {
                throw exception;
            }
        }


        public bool Delete()
        {
            if (this.gcgbCommandMaster.GCGBCommandID <= 0) return false;

            try
            {

                if (!this.SaveValidate()) throw new System.ArgumentException("Insufficient delete", "Delete validate");

                using (TransactionScope transactionScope = new TransactionScope())
                {
                    if (!this.Editable) throw new System.ArgumentException("Insufficient delete", "Uneditable");

                    if (!this.SaveUndo(this.gcgbCommandMaster.GCGBCommandID)) throw new System.ArgumentException("Insufficient delete", "Delete detail");

                    if (this.MasterTableAdapter.Delete(this.gcgbCommandMaster.GCGBCommandID) != 1) throw new System.ArgumentException("Insufficient delete", "Delete master");

                    if (this.SaveConflict()) throw new System.ArgumentException("Insufficient delete", "Delete conflict");

                    transactionScope.Complete();                    
                }
                this.Fill(0);
                return true;
            }
            catch (System.Exception exception)
            {
                throw exception;
            }
        }


        private bool SaveValidate()
        {
            ExceptionTable exceptionTable = new ExceptionTable(new string[2, 2] { { "ExceptionCategory", "System.String" }, { "ExceptionDescription", "System.String" } });

            this.UserOrganization = GlobalUserPermission.GetUserInformation(this.GCGBCommandMaster.PreparedEmployeeID, this.GCGBCommandMaster.GCGBCommandDate);

            if (this.UserOrganization.UserID <= 0 || this.UserOrganization.UserOrganizationID <= 0) exceptionTable.AddException(new string[] { GlobalVariables.stringFieldRequired, "User information" });

            if (exceptionTable.Table.Rows.Count <= 0 && this.IsValid) return true; else throw new CustomException("Save validate", exceptionTable.Table);
        }


        private bool SaveMaster(ref int gcgbCommandID)
        {
            GCGBCommandDTS.GCGBCommandMasterDataTable masterDataTable;
            GCGBCommandDTS.GCGBCommandMasterRow masterRow;

            if (this.gcgbCommandMaster.GCGBCommandID <= 0) //Add
            {
                masterDataTable = new GCGBCommandDTS.GCGBCommandMasterDataTable();
                masterRow = masterDataTable.NewGCGBCommandMasterRow();
            }
            else //Edit
            {
                if (!this.SaveUndo(gcgbCommandMaster.GCGBCommandID)) throw new System.ArgumentException("Insufficient save", "Save undo");
                masterDataTable = this.MasterTableAdapter.GetData(gcgbCommandMaster.GCGBCommandID);
                if (masterDataTable.Count > 0) masterRow = masterDataTable[0]; else throw new System.ArgumentException("Insufficient save", "Get for edit");
            }

            masterRow.GCGBCommandTypeID = this.gcgbCommandMaster.GCGBCommandTypeID;
            masterRow.GCGBCommandDate = this.gcgbCommandMaster.GCGBCommandDate;
            masterRow.EntryDate = this.gcgbCommandMaster.EntryDate;
            masterRow.GCGBCommandReference = this.gcgbCommandMaster.GCGBCommandReference;
            masterRow.Description = this.gcgbCommandMaster.Description;
            masterRow.PreparedEmployeeID = this.gcgbCommandMaster.PreparedEmployeeID;
            masterRow.ApprovedEmployeeID = this.gcgbCommandMaster.ApprovedEmployeeID;
            masterRow.Remarks = this.gcgbCommandMaster.Remarks;

            masterRow.TotalQuantity = 0;
            masterRow.TotalQuantityPiece = 0;
            masterRow.Approved = true;
            masterRow.ApprovedDate = DateTime.MaxValue;
            masterRow.InActive = false;
            masterRow.InActivePartial = false;
            masterRow.InActiveDate = DateTime.MaxValue;

            masterRow.UserID = this.UserOrganization.UserID;
            masterRow.UserOrganizationID = this.UserOrganization.UserOrganizationID;

            if (this.gcgbCommandMaster.GCGBCommandID <= 0) masterDataTable.AddGCGBCommandMasterRow(masterRow);

            int rowsAffected = this.MasterTableAdapter.Update(masterRow);

            gcgbCommandID = masterRow.GCGBCommandID;

            return rowsAffected == 1;

        }


        private bool SaveDetail(int gcgbCommandID)
        {
            int serialID = 0; int rowsAffected = 0;


            #region <Save collection>

            serialID = 0;

            GCGBCommandDTS.GCGBCommandCollectionDataTable collectionDataTable = new GCGBCommandDTS.GCGBCommandCollectionDataTable();

            foreach (GCGBCommandCollection gcgbCommandCollection in this.gcgbCommandCollectionList)
            {
                GCGBCommandDTS.GCGBCommandCollectionRow collectionRow = collectionDataTable.NewGCGBCommandCollectionRow();

                collectionRow.GCGBCommandID = gcgbCommandID;
                collectionRow.SerialID = ++serialID;

                collectionRow.MaterialSetID = gcgbCommandCollection.MaterialSetID;
                collectionRow.GCGBCommandTypeID = this.GCGBCommandMaster.GCGBCommandTypeID;

                collectionRow.GCGBCommandDate = this.GCGBCommandMaster.GCGBCommandDate;
                collectionRow.GCGBCommandReference = this.GCGBCommandMaster.GCGBCommandReference;
                collectionRow.Quantity = gcgbCommandCollection.Quantity;
                collectionRow.QuantityWHOutput = 0;
                collectionRow.QuantityWHOutputLoss = 0;

                collectionRow.MaterialSemiID1 = gcgbCommandCollection.MaterialSemiID1;
                collectionRow.QuantityPerSet1 = gcgbCommandCollection.QuantityPerSet1;
                collectionRow.QuantitySemi1 = gcgbCommandCollection.QuantitySemi1;
                collectionRow.QuantitySemiWHOutputBad1 = 0;
                collectionRow.QuantitySemiWHOutputLoss1 = 0;

                collectionRow.MaterialSemiID2 = gcgbCommandCollection.MaterialSemiID2;
                collectionRow.QuantityPerSet2 = gcgbCommandCollection.QuantityPerSet2;
                collectionRow.QuantitySemi2 = gcgbCommandCollection.QuantitySemi2;
                collectionRow.QuantitySemiWHOutputBad2 = 0;
                collectionRow.QuantitySemiWHOutputLoss2 = 0;

                collectionRow.MaterialSemiID3 = gcgbCommandCollection.MaterialSemiID3;
                collectionRow.QuantityPerSet3 = gcgbCommandCollection.QuantityPerSet3;
                collectionRow.QuantitySemi3 = gcgbCommandCollection.QuantitySemi3;
                collectionRow.QuantitySemiWHOutputBad3 = 0;
                collectionRow.QuantitySemiWHOutputLoss3 = 0;

                collectionRow.Remarks = gcgbCommandCollection.Remarks;

                collectionRow.Approved = true;
                collectionRow.InActive = false;

                collectionRow.QuantityPlaster = 0;
                collectionRow.QuantityPlasterLoss = 0;
                collectionRow.QuantitySemiPlasterBad1 = 0;
                collectionRow.QuantitySemiPlasterLoss1 = 0;
                collectionRow.QuantitySemiPlasterPreviousMinus1 = 0;                
                collectionRow.QuantitySemiPlasterBad2 = 0;
                collectionRow.QuantitySemiPlasterLoss2 = 0;
                collectionRow.QuantitySemiPlasterPreviousMinus2 = 0;
                collectionRow.QuantitySemiPlasterBad3 = 0;
                collectionRow.QuantitySemiPlasterLoss3 = 0;
                collectionRow.QuantitySemiPlasterPreviousMinus3 = 0;

                collectionRow.QuantityStaple = 0;
                collectionRow.QuantityStapleLoss = 0;
                collectionRow.QuantitySemiStapleBad1 = 0;
                collectionRow.QuantitySemiStapleLoss1 = 0;
                collectionRow.QuantitySemiStaplePreviousMinus1 = 0;
                collectionRow.QuantitySemiStapleBad2 = 0;
                collectionRow.QuantitySemiStapleLoss2 = 0;
                collectionRow.QuantitySemiStaplePreviousMinus2 = 0;
                collectionRow.QuantitySemiStapleBad3 = 0;
                collectionRow.QuantitySemiStapleLoss3 = 0;
                collectionRow.QuantitySemiStaplePreviousMinus3 = 0;

                collectionRow.QuantityClipping = 0;
                collectionRow.QuantityClippingLoss = 0;
                collectionRow.QuantitySemiClippingBad1 = 0;
                collectionRow.QuantitySemiClippingLoss1 = 0;
                collectionRow.QuantitySemiClippingPreviousMinus1 = 0;
                collectionRow.QuantitySemiClippingBad2 = 0;
                collectionRow.QuantitySemiClippingLoss2 = 0;
                collectionRow.QuantitySemiClippingPreviousMinus2 = 0;
                collectionRow.QuantitySemiClippingBad3 = 0;
                collectionRow.QuantitySemiClippingLoss3 = 0;
                collectionRow.QuantitySemiClippingPreviousMinus3 = 0;      

                collectionDataTable.AddGCGBCommandCollectionRow(collectionRow);
            }

            rowsAffected = this.CollectionTableAdapter.Update(collectionDataTable);
            if (rowsAffected != this.gcgbCommandCollectionList.Count) throw new System.ArgumentException("Insufficient save", "Save detail: Item Category");


            #endregion <Save collection>


            return true;
        }


        private bool SaveUndo(int gcgbCommandID)
        {
            this.CollectionTableAdapter.Delete(gcgbCommandID);

            return true;
        }


        private bool SaveConflict()
        {
            return false;
        }



        #endregion



        #region Import Excel

        public bool ImportExcel(string fileName)
        {
            try
            {
                DateTime dateTimeValue; string gcgbCommandReference = "";
                ExceptionTable exceptionTable = new ExceptionTable(new string[2, 2] { { "ExceptionCategory", "System.String" }, { "ExceptionDescription", "System.String" } });

                TimeSpan timeout = TimeSpan.FromMinutes(90);
                using (TransactionScope transactionScope = new TransactionScope(TransactionScopeOption.Required, timeout))
                {
                    if (this.ReadOnly) throw new System.ArgumentException("Import", "Permission conflict");

                    DataTable excelDataTable = OleDbDatabase.OpenExcelSheet(OleDbDatabase.MappingTaskID.GCGBCommand, fileName);
                    if (excelDataTable != null && excelDataTable.Rows.Count > 0)
                    {
                        string importGCGBCommandID = "";
                        GCGBCommandBLL importGCGBCommandBLL = new GCGBCommandBLL();

                        foreach (DataRow excelDataRow in excelDataTable.Rows)
                        {
                            if (importGCGBCommandID != excelDataRow["GCGBCommandID"].ToString().Trim())
                            {//Start a new importGCGBCommandBLL
                                importGCGBCommandID = excelDataRow["GCGBCommandID"].ToString().Trim();

                                //Save current importGCGBCommandBLL if existing
                                if (!exceptionTable.IsDirty)
                                    if (importGCGBCommandBLL.IsDirty && !importGCGBCommandBLL.Save())
                                        exceptionTable.AddException(new string[] { "Insufficient save", gcgbCommandReference });

                                //Start a new importGCGBCommandBLL
                                importGCGBCommandBLL.New();
                                exceptionTable.ClearDirty();



                                //ERmgrVCP.ListGCGBCommandTypeRow gcgbCommandTypeRow = CommonDatabaseMethod.GetMarketingPaymentType(0, excelDataRow["MarketingPaymentTypeID"].ToString().Trim());
                                //if (gcgbCommandTypeRow != null)
                                //    importGCGBCommandBLL.gcgbCommandMaster.GCGBCommandTypeID = gcgbCommandTypeRow.GCGBCommandTypeID;
                                //else
                                //{//Handle exception when MarketingPaymentType can not be found
                                //    exceptionTable.AddException(new string[] { "Payment Type", excelDataRow["GCGBCommandTypeID"].ToString() });
                                //}


                                if (DateTime.TryParse(excelDataRow["GCGBCommandDate"].ToString(), out dateTimeValue)) importGCGBCommandBLL.gcgbCommandMaster.GCGBCommandDate = dateTimeValue; else exceptionTable.AddException(new string[] { "Invalid Date Value", excelDataRow["RequestedDate"].ToString() });
                                if (DateTime.TryParse(excelDataRow["EntryDate"].ToString(), out dateTimeValue)) importGCGBCommandBLL.gcgbCommandMaster.EntryDate = dateTimeValue; else exceptionTable.AddException(new string[] { "Invalid Date Value", excelDataRow["EntryDate"].ToString() });

                                importGCGBCommandBLL.gcgbCommandMaster.GCGBCommandReference = excelDataRow["GCGBCommandReference"].ToString();
                                importGCGBCommandBLL.gcgbCommandMaster.Description = excelDataRow["Description"].ToString();

                                ERmgrVCP.SPGetSalesmenNameRow salesmenNameRow = CommonDatabaseMethod.GetSalesmenName(0, excelDataRow["PreparedEmployeeID"].ToString().Trim());
                                if (salesmenNameRow != null)
                                    importGCGBCommandBLL.gcgbCommandMaster.PreparedEmployeeID = salesmenNameRow.SalesmenID;
                                else
                                {//Handle exception when ItemType can not be found
                                    exceptionTable.AddException(new string[] { "Staffs", excelDataRow["PreparedEmployeeID"].ToString() });
                                }


                                salesmenNameRow = CommonDatabaseMethod.GetSalesmenName(0, excelDataRow["ApprovedEmployeeID"].ToString().Trim());
                                if (salesmenNameRow != null)
                                    importGCGBCommandBLL.gcgbCommandMaster.ApprovedEmployeeID = salesmenNameRow.SalesmenID;
                                else
                                {//Handle exception when ItemType can not be found
                                    exceptionTable.AddException(new string[] { "Staffs", excelDataRow["ApprovedEmployeeID"].ToString() });
                                }

                                importGCGBCommandBLL.gcgbCommandMaster.Remarks = excelDataRow["Remarks"].ToString();
                            }

                            #region Import Detail Collection

                            ERmgrVCP.SPGetCustomerNameRow customerNameRow = CommonDatabaseMethod.GetCustomerName(0, excelDataRow["CustomerID"].ToString().Trim());
                            //if (customerNameRow != null)
                            //{
                            //    ERmgrVCP.SPGetMarketingPaymentTermRow marketingPaymentTermRow = CommonDatabaseMethod.GetMarketingPaymentTerm(0, excelDataRow["MarketingPaymentTermID"].ToString().Trim());
                            //    if (marketingPaymentTermRow != null)
                            //        importGCGBCommandBLL.GCGBCommandCollectionList.Add(new GCGBCommandCollection(customerNameRow.CustomerID, 0, marketingPaymentTermRow.MarketingPaymentTermID, (double)excelDataRow["TurnOverAmount"], (double)excelDataRow["IncentiveAmount"], 0, 0, 0, 0, 0, 0, excelDataRow["DetailRemarks"].ToString().Trim()));
                            //    else
                            //    {//Handle exception when MarketingPaymentTerm can not be found
                            //        exceptionTable.AddException(new string[] { "Payment Term", excelDataRow["MarketingPaymentTermID"].ToString() });
                            //    }
                            //}

                            //else
                            //{//Handle exception when CustomerName can not be found
                            //    exceptionTable.AddException(new string[] { "Customer", excelDataRow["CustomerID"].ToString() });
                            //}

                            #endregion Import Detail Collection

                        }

                        //Save the last importGCGBCommandBLL after loop for all excelDataRow
                        if (!exceptionTable.IsDirty)
                            if (importGCGBCommandBLL.IsDirty && !importGCGBCommandBLL.Save())
                                exceptionTable.AddException(new string[] { "Insufficient save", gcgbCommandReference });
                        //--------------OK

                    }
                    if (exceptionTable.Table.Rows.Count <= 0)
                        transactionScope.Complete();
                    else
                        throw new CustomException("Invalid data value or data entry not found", exceptionTable.Table);

                }
                return true;
            }
            catch (System.Exception exception)
            {
                throw exception;
            }

        }

        #endregion Import Excel



        private void RestoreProcedure()
        {
            string queryString;
            string[] queryArray;


            queryString = "     @LowerFillterDate DateTime, @UpperFillterDate DateTime " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       SELECT      LEFT(DATENAME(Month, GCGBCommandMaster.GCGBCommandDate), 3) + ', ' + DATENAME(Year, GCGBCommandMaster.GCGBCommandDate) AS Month, * " + "\r\n";
            queryString = queryString + "       FROM        GCGBCommandMaster " + "\r\n";
            queryString = queryString + "       WHERE       GCGBCommandMaster.GCGBCommandDate >= @LowerFillterDate AND GCGBCommandMaster.GCGBCommandDate <= @UpperFillterDate " + "\r\n";

            SQLDatabase.CreateStoredProcedure("GCGBCommandListing", queryString);





            queryString = "     @GCGBCommandID Int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       SELECT      GCGBCommandID, GCGBCommandReference, GCGBCommandDate, GCGBCommandTypeID, EntryDate, PreparedEmployeeID, ApprovedEmployeeID, UserID, UserOrganizationID, Description, Remarks, TotalQuantity, TotalQuantityPiece, Approved, ApprovedDate, InActive, InActivePartial, InActiveDate " + "\r\n";
            queryString = queryString + "       FROM        GCGBCommandMaster " + "\r\n";
            queryString = queryString + "       WHERE       GCGBCommandID = @GCGBCommandID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("GCGBCommandMasterSelect", queryString);


            queryString = "     @GCGBCommandReference nvarchar(50), @GCGBCommandDate datetime, @GCGBCommandTypeID int, @EntryDate datetime, @PreparedEmployeeID int, @ApprovedEmployeeID int, @UserID int, @UserOrganizationID int, @Description nvarchar(50), @Remarks nvarchar(150), @TotalQuantity float, @TotalQuantityPiece float, @Approved bit, @ApprovedDate datetime, @InActive bit, @InActivePartial bit, @InActiveDate datetime " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       INSERT INTO     GCGBCommandMaster (GCGBCommandReference, GCGBCommandDate, GCGBCommandTypeID, EntryDate, PreparedEmployeeID, ApprovedEmployeeID, UserID, UserOrganizationID, Description, Remarks, TotalQuantity, TotalQuantityPiece, Approved, ApprovedDate, InActive, InActivePartial, InActiveDate) VALUES (@GCGBCommandReference, @GCGBCommandDate, @GCGBCommandTypeID, @EntryDate, @PreparedEmployeeID, @ApprovedEmployeeID, @UserID, @UserOrganizationID, @Description, @Remarks, @TotalQuantity, @TotalQuantityPiece, @Approved, @ApprovedDate, @InActive, @InActivePartial, @InActiveDate) " + "\r\n";
            queryString = queryString + "       SELECT          GCGBCommandID, GCGBCommandReference, GCGBCommandDate, GCGBCommandTypeID, EntryDate, PreparedEmployeeID, ApprovedEmployeeID, UserID, UserOrganizationID, Description, Remarks, TotalQuantity, TotalQuantityPiece, Approved, ApprovedDate, InActive, InActivePartial, InActiveDate FROM GCGBCommandMaster WHERE GCGBCommandID = SCOPE_IDENTITY() " + "\r\n";

            SQLDatabase.CreateStoredProcedure("GCGBCommandMasterInsert", queryString);


            queryString = " @GCGBCommandID int, @GCGBCommandReference nvarchar(50), @GCGBCommandDate datetime, @GCGBCommandTypeID int, @EntryDate datetime, @PreparedEmployeeID int, @ApprovedEmployeeID int, @UserID int, @UserOrganizationID int, @Description nvarchar(50), @Remarks nvarchar(150), @TotalQuantity float, @TotalQuantityPiece float, @Approved bit, @ApprovedDate datetime, @InActive bit, @InActivePartial bit, @InActiveDate datetime " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       UPDATE      GCGBCommandMaster SET GCGBCommandReference = @GCGBCommandReference, GCGBCommandDate = @GCGBCommandDate, GCGBCommandTypeID = @GCGBCommandTypeID, EntryDate = @EntryDate, PreparedEmployeeID = @PreparedEmployeeID, ApprovedEmployeeID = @ApprovedEmployeeID, UserID = @UserID, UserOrganizationID = @UserOrganizationID, Description = @Description, Remarks = @Remarks, TotalQuantity = @TotalQuantity, TotalQuantityPiece = @TotalQuantityPiece, Approved = @Approved, ApprovedDate = @ApprovedDate, InActive = @InActive, InActivePartial = @InActivePartial, InActiveDate = @InActiveDate WHERE GCGBCommandID = @GCGBCommandID " + "\r\n";
            queryString = queryString + "       SELECT      GCGBCommandID, GCGBCommandReference, GCGBCommandDate, GCGBCommandTypeID, EntryDate, PreparedEmployeeID, ApprovedEmployeeID, UserID, UserOrganizationID, Description, Remarks, TotalQuantity, TotalQuantityPiece, Approved, ApprovedDate, InActive, InActivePartial, InActiveDate FROM GCGBCommandMaster WHERE GCGBCommandID = @GCGBCommandID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("GCGBCommandMasterUpdate", queryString);


            queryString = " @GCGBCommandID int ";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       DELETE FROM GCGBCommandMaster WHERE GCGBCommandID = @GCGBCommandID " + "\r\n";
            SQLDatabase.CreateStoredProcedure("GCGBCommandMasterDelete", queryString);





            queryString = " @GCGBCommandID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       SELECT      GCGBCommandID, SerialID, GCGBCommandReference, GCGBCommandDate, GCGBCommandTypeID, MaterialSetID, Quantity, QuantityWHOutput, QuantityWHOutputLoss, MaterialSemiID1, QuantityPerSet1, QuantitySemi1, QuantitySemiWHOutputBad1, QuantitySemiWHOutputLoss1, MaterialSemiID2, QuantityPerSet2, QuantitySemi2, QuantitySemiWHOutputBad2, QuantitySemiWHOutputLoss2, MaterialSemiID3, QuantityPerSet3, QuantitySemi3, QuantitySemiWHOutputBad3, QuantitySemiWHOutputLoss3, Remarks, Approved, InActive, " + "\r\n";
            queryString = queryString + "             QuantityPlaster, QuantityPlasterLoss, QuantitySemiPlasterBad1, QuantitySemiPlasterLoss1, QuantitySemiPlasterPreviousMinus1, QuantitySemiPlasterBad2, QuantitySemiPlasterLoss2, QuantitySemiPlasterPreviousMinus2, QuantitySemiPlasterBad3, QuantitySemiPlasterLoss3, QuantitySemiPlasterPreviousMinus3,    QuantityStaple, QuantityStapleLoss, QuantitySemiStapleBad1, QuantitySemiStapleLoss1, QuantitySemiStaplePreviousMinus1, QuantitySemiStapleBad2, QuantitySemiStapleLoss2, QuantitySemiStaplePreviousMinus2, QuantitySemiStapleBad3, QuantitySemiStapleLoss3, QuantitySemiStaplePreviousMinus3," + "\r\n";
            queryString = queryString + "             QuantityClipping, QuantityClippingLoss, QuantitySemiClippingBad1, QuantitySemiClippingLoss1, QuantitySemiClippingPreviousMinus1, QuantitySemiClippingBad2, QuantitySemiClippingLoss2, QuantitySemiClippingPreviousMinus2, QuantitySemiClippingBad3, QuantitySemiClippingLoss3, QuantitySemiClippingPreviousMinus3" + "\r\n";
            queryString = queryString + "       FROM        GCGBCommandCollection " + "\r\n";
            queryString = queryString + "       WHERE       GCGBCommandID = @GCGBCommandID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("GCGBCommandCollectionSelect", queryString);


            queryString = " @GCGBCommandID int,	@SerialID int, @GCGBCommandReference nvarchar(50), @GCGBCommandDate datetime, @GCGBCommandTypeID int, @MaterialSetID int, @Quantity float, @QuantityWHOutput float, @QuantityWHOutputLoss float, @MaterialSemiID1 int, @QuantityPerSet1 float, @QuantitySemi1 float, @QuantitySemiWHOutputBad1 float, @QuantitySemiWHOutputLoss1 float, @MaterialSemiID2 int, @QuantityPerSet2 float, @QuantitySemi2 float, @QuantitySemiWHOutputBad2 float, @QuantitySemiWHOutputLoss2 float, @MaterialSemiID3 int, @QuantityPerSet3 float, @QuantitySemi3 float, @QuantitySemiWHOutputBad3 float, @QuantitySemiWHOutputLoss3 float, @Remarks nvarchar(150), @Approved bit, @InActive bit, " + "\r\n";
            queryString = queryString + "   @QuantityPlaster float, @QuantityPlasterLoss float, @QuantitySemiPlasterBad1 float, @QuantitySemiPlasterLoss1 float, @QuantitySemiPlasterPreviousMinus1 float, @QuantitySemiPlasterBad2 float, @QuantitySemiPlasterLoss2 float, @QuantitySemiPlasterPreviousMinus2 float, @QuantitySemiPlasterBad3 float, @QuantitySemiPlasterLoss3 float, @QuantitySemiPlasterPreviousMinus3 float,   @QuantityStaple float, @QuantityStapleLoss float, @QuantitySemiStapleBad1 float, @QuantitySemiStapleLoss1 float, @QuantitySemiStaplePreviousMinus1 float, @QuantitySemiStapleBad2 float, @QuantitySemiStapleLoss2 float, @QuantitySemiStaplePreviousMinus2 float, @QuantitySemiStapleBad3 float, @QuantitySemiStapleLoss3 float, @QuantitySemiStaplePreviousMinus3 float, " + "\r\n";
            queryString = queryString + "   @QuantityClipping float, @QuantityClippingLoss float, @QuantitySemiClippingBad1 float, @QuantitySemiClippingLoss1 float, @QuantitySemiClippingPreviousMinus1 float, @QuantitySemiClippingBad2 float, @QuantitySemiClippingLoss2 float, @QuantitySemiClippingPreviousMinus2 float, @QuantitySemiClippingBad3 float, @QuantitySemiClippingLoss3 float, @QuantitySemiClippingPreviousMinus3 float " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       INSERT INTO GCGBCommandCollection (GCGBCommandID, SerialID, GCGBCommandReference, GCGBCommandDate, GCGBCommandTypeID, MaterialSetID, Quantity, QuantityWHOutput, QuantityWHOutputLoss, MaterialSemiID1, QuantityPerSet1, QuantitySemi1, QuantitySemiWHOutputBad1, QuantitySemiWHOutputLoss1, MaterialSemiID2, QuantityPerSet2, QuantitySemi2, QuantitySemiWHOutputBad2, QuantitySemiWHOutputLoss2, MaterialSemiID3, QuantityPerSet3, QuantitySemi3, QuantitySemiWHOutputBad3, QuantitySemiWHOutputLoss3, Remarks, Approved, InActive, " + "\r\n";
            queryString = queryString + "           QuantityPlaster, QuantityPlasterLoss, QuantitySemiPlasterBad1, QuantitySemiPlasterLoss1, QuantitySemiPlasterPreviousMinus1, QuantitySemiPlasterBad2, QuantitySemiPlasterLoss2, QuantitySemiPlasterPreviousMinus2, QuantitySemiPlasterBad3, QuantitySemiPlasterLoss3, QuantitySemiPlasterPreviousMinus3,    QuantityStaple, QuantityStapleLoss, QuantitySemiStapleBad1, QuantitySemiStapleLoss1, QuantitySemiStaplePreviousMinus1, QuantitySemiStapleBad2, QuantitySemiStapleLoss2, QuantitySemiStaplePreviousMinus2, QuantitySemiStapleBad3, QuantitySemiStapleLoss3, QuantitySemiStaplePreviousMinus3, " + "\r\n";
            queryString = queryString + "           QuantityClipping, QuantityClippingLoss, QuantitySemiClippingBad1, QuantitySemiClippingLoss1, QuantitySemiClippingPreviousMinus1, QuantitySemiClippingBad2, QuantitySemiClippingLoss2, QuantitySemiClippingPreviousMinus2, QuantitySemiClippingBad3, QuantitySemiClippingLoss3, QuantitySemiClippingPreviousMinus3) " + "\r\n";
            queryString = queryString + "       VALUES (@GCGBCommandID, @SerialID, @GCGBCommandReference, @GCGBCommandDate, @GCGBCommandTypeID, @MaterialSetID, @Quantity, @QuantityWHOutput, @QuantityWHOutputLoss, @MaterialSemiID1, @QuantityPerSet1, @QuantitySemi1, @QuantitySemiWHOutputBad1, @QuantitySemiWHOutputLoss1, @MaterialSemiID2, @QuantityPerSet2, @QuantitySemi2, @QuantitySemiWHOutputBad2, @QuantitySemiWHOutputLoss2, @MaterialSemiID3, @QuantityPerSet3, @QuantitySemi3, @QuantitySemiWHOutputBad3, @QuantitySemiWHOutputLoss3, @Remarks, @Approved, @InActive, " + "\r\n";
            queryString = queryString + "           @QuantityPlaster, @QuantityPlasterLoss, @QuantitySemiPlasterBad1, @QuantitySemiPlasterLoss1, @QuantitySemiPlasterPreviousMinus1, @QuantitySemiPlasterBad2, @QuantitySemiPlasterLoss2, @QuantitySemiPlasterPreviousMinus2, @QuantitySemiPlasterBad3, @QuantitySemiPlasterLoss3, @QuantitySemiPlasterPreviousMinus3,   @QuantityStaple, @QuantityStapleLoss, @QuantitySemiStapleBad1, @QuantitySemiStapleLoss1, @QuantitySemiStaplePreviousMinus1, @QuantitySemiStapleBad2, @QuantitySemiStapleLoss2, @QuantitySemiStaplePreviousMinus2, @QuantitySemiStapleBad3, @QuantitySemiStapleLoss3, @QuantitySemiStaplePreviousMinus3," + "\r\n";
            queryString = queryString + "           @QuantityClipping, @QuantityClippingLoss, @QuantitySemiClippingBad1, @QuantitySemiClippingLoss1, @QuantitySemiClippingPreviousMinus1, @QuantitySemiClippingBad2, @QuantitySemiClippingLoss2, @QuantitySemiClippingPreviousMinus2, @QuantitySemiClippingBad3, @QuantitySemiClippingLoss3, @QuantitySemiClippingPreviousMinus3) " + "\r\n"; 
            queryString = queryString + "       SELECT      * FROM GCGBCommandCollection WHERE GCGBCommandID = @GCGBCommandID AND SerialID = @SerialID " + "\r\n";
    
            SQLDatabase.CreateStoredProcedure("GCGBCommandCollectionInsert", queryString);


            queryString = " @GCGBCommandID int,	@SerialID int, @GCGBCommandReference nvarchar(50), @GCGBCommandDate datetime, @GCGBCommandTypeID int, @MaterialSetID int, @Quantity float, @QuantityWHOutput float, @QuantityWHOutputLoss float, @MaterialSemiID1 int, @QuantityPerSet1 float, @QuantitySemi1 float, @QuantitySemiWHOutputBad1 float, @QuantitySemiWHOutputLoss1 float, @MaterialSemiID2 int, @QuantityPerSet2 float, @QuantitySemi2 float, @QuantitySemiWHOutputBad2 float, @QuantitySemiWHOutputLoss2 float, @MaterialSemiID3 int, @QuantityPerSet3 float, @QuantitySemi3 float, @QuantitySemiWHOutputBad3 float, @QuantitySemiWHOutputLoss3 float, @Remarks nvarchar(150), @Approved bit, @InActive bit, " + "\r\n";
            queryString = queryString + "   @QuantityPlaster float, @QuantityPlasterLoss float, @QuantitySemiPlasterBad1 float, @QuantitySemiPlasterLoss1 float, @QuantitySemiPlasterPreviousMinus1 float, @QuantitySemiPlasterBad2 float, @QuantitySemiPlasterLoss2 float, @QuantitySemiPlasterPreviousMinus2 float, @QuantitySemiPlasterBad3 float, @QuantitySemiPlasterLoss3 float, @QuantitySemiPlasterPreviousMinus3 float,   @QuantityStaple float, @QuantityStapleLoss float, @QuantitySemiStapleBad1 float, @QuantitySemiStapleLoss1 float, @QuantitySemiStaplePreviousMinus1 float, @QuantitySemiStapleBad2 float, @QuantitySemiStapleLoss2 float, @QuantitySemiStaplePreviousMinus2 float, @QuantitySemiStapleBad3 float, @QuantitySemiStapleLoss3 float, @QuantitySemiStaplePreviousMinus3 float, " + "\r\n";
            queryString = queryString + "   @QuantityClipping float, @QuantityClippingLoss float, @QuantitySemiClippingBad1 float, @QuantitySemiClippingLoss1 float, @QuantitySemiClippingPreviousMinus1 float, @QuantitySemiClippingBad2 float, @QuantitySemiClippingLoss2 float, @QuantitySemiClippingPreviousMinus2 float, @QuantitySemiClippingBad3 float, @QuantitySemiClippingLoss3 float, @QuantitySemiClippingPreviousMinus3 float " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       UPDATE      GCGBCommandCollection SET GCGBCommandReference = @GCGBCommandReference, GCGBCommandDate = @GCGBCommandDate, GCGBCommandTypeID = @GCGBCommandTypeID, MaterialSetID = @MaterialSetID, Quantity = @Quantity, QuantityWHOutput = @QuantityWHOutput, QuantityWHOutputLoss = @QuantityWHOutputLoss, MaterialSemiID1 = @MaterialSemiID1, QuantityPerSet1 = @QuantityPerSet1, QuantitySemi1 = @QuantitySemi1, QuantitySemiWHOutputBad1 = @QuantitySemiWHOutputBad1, QuantitySemiWHOutputLoss1 = @QuantitySemiWHOutputLoss1, MaterialSemiID2 = @MaterialSemiID2, QuantityPerSet2 = @QuantityPerSet2, QuantitySemi2 = @QuantitySemi2, QuantitySemiWHOutputBad2 = @QuantitySemiWHOutputBad2, QuantitySemiWHOutputLoss2 = @QuantitySemiWHOutputLoss2, MaterialSemiID3 = @MaterialSemiID3, QuantityPerSet3 = @QuantityPerSet3, QuantitySemi3 = @QuantitySemi3, QuantitySemiWHOutputBad3 = @QuantitySemiWHOutputBad3, QuantitySemiWHOutputLoss3 = @QuantitySemiWHOutputLoss3, Remarks = @Remarks, Approved = @Approved, InActive = @InActive, " + "\r\n";
            queryString = queryString + "             QuantityPlaster = @QuantityPlaster, QuantityPlasterLoss = @QuantityPlasterLoss, QuantitySemiPlasterBad1 = @QuantitySemiPlasterBad1, QuantitySemiPlasterLoss1 = @QuantitySemiPlasterLoss1, QuantitySemiPlasterPreviousMinus1 = @QuantitySemiPlasterPreviousMinus1, QuantitySemiPlasterBad2 = @QuantitySemiPlasterBad2, QuantitySemiPlasterLoss2 = @QuantitySemiPlasterLoss2, QuantitySemiPlasterPreviousMinus2 = @QuantitySemiPlasterPreviousMinus2, QuantitySemiPlasterBad3 = @QuantitySemiPlasterBad3, QuantitySemiPlasterLoss3 = @QuantitySemiPlasterLoss3, QuantitySemiPlasterPreviousMinus3 = @QuantitySemiPlasterPreviousMinus3, " + "\r\n";
            queryString = queryString + "             QuantityStaple = @QuantityStaple, QuantityStapleLoss = @QuantityStapleLoss, QuantitySemiStapleBad1 = @QuantitySemiStapleBad1, QuantitySemiStapleLoss1 = @QuantitySemiStapleLoss1, QuantitySemiStaplePreviousMinus1 = @QuantitySemiStaplePreviousMinus1, QuantitySemiStapleBad2 = @QuantitySemiStapleBad2, QuantitySemiStapleLoss2 = @QuantitySemiStapleLoss2, QuantitySemiStaplePreviousMinus2 = @QuantitySemiStaplePreviousMinus2, QuantitySemiStapleBad3 = @QuantitySemiStapleBad3, QuantitySemiStapleLoss3 = @QuantitySemiStapleLoss3, QuantitySemiStaplePreviousMinus3 = @QuantitySemiStaplePreviousMinus3, " + "\r\n";
            queryString = queryString + "             QuantityClipping = @QuantityClipping, QuantityClippingLoss = @QuantityClippingLoss, QuantitySemiClippingBad1 = @QuantitySemiClippingBad1, QuantitySemiClippingLoss1 = @QuantitySemiClippingLoss1, QuantitySemiClippingPreviousMinus1 = @QuantitySemiClippingPreviousMinus1, QuantitySemiClippingBad2 = @QuantitySemiClippingBad2, QuantitySemiClippingLoss2 = @QuantitySemiClippingLoss2, QuantitySemiClippingPreviousMinus2 = @QuantitySemiClippingPreviousMinus2, QuantitySemiClippingBad3 = @QuantitySemiClippingBad3, QuantitySemiClippingLoss3 = @QuantitySemiClippingLoss3, QuantitySemiClippingPreviousMinus3 = @QuantitySemiClippingPreviousMinus3 " + "\r\n";
            queryString = queryString + "       WHERE GCGBCommandID = @GCGBCommandID AND SerialID = @SerialID " + "\r\n";
            queryString = queryString + "       SELECT      * FROM GCGBCommandCollection WHERE GCGBCommandID = @GCGBCommandID AND SerialID = @SerialID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("GCGBCommandCollectionUpdate", queryString);

            
            queryString = " @GCGBCommandID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       DELETE FROM GCGBCommandCollection WHERE GCGBCommandID = @GCGBCommandID " + "\r\n";
            SQLDatabase.CreateStoredProcedure("GCGBCommandCollectionDelete", queryString);



            queryString = " @GCGBCommandID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       SELECT      GCGBCommandID, SerialID, GCGBCommandCollectionSerialID, GCGBCommandDate, MaterialSetID, Quantity, QuantityOutput, MaterialSemiID, QuantityPerSet, QuantitySemi, Remarks, Approved, InActive " + "\r\n";
            queryString = queryString + "       FROM        GCGBCommandDetail " + "\r\n";
            queryString = queryString + "       WHERE       GCGBCommandID = @GCGBCommandID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("GCGBCommandDetailSelect", queryString);


            queryString = " @GCGBCommandID int, @SerialID int, @GCGBCommandCollectionSerialID int, @GCGBCommandDate datetime, @MaterialSetID int, @Quantity float, @QuantityOutput float, @MaterialSemiID int, @QuantityPerSet float, @QuantitySemi float, @Remarks nvarchar(150), @Approved bit, @InActive bit " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       INSERT INTO GCGBCommandDetail (GCGBCommandID, SerialID, GCGBCommandCollectionSerialID, GCGBCommandDate, MaterialSetID, Quantity, QuantityOutput, MaterialSemiID, QuantityPerSet, QuantitySemi, Remarks, Approved, InActive) VALUES (@GCGBCommandID, @SerialID, @GCGBCommandCollectionSerialID, @GCGBCommandDate, @MaterialSetID, @Quantity, @QuantityOutput, @MaterialSemiID, @QuantityPerSet, @QuantitySemi, @Remarks, @Approved, @InActive) " + "\r\n";
            queryString = queryString + "       SELECT      GCGBCommandID, SerialID, GCGBCommandCollectionSerialID, GCGBCommandDate, MaterialSetID, Quantity, QuantityOutput, MaterialSemiID, QuantityPerSet, QuantitySemi, Remarks, Approved, InActive FROM GCGBCommandDetail WHERE GCGBCommandID = @GCGBCommandID AND SerialID = @SerialID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("GCGBCommandDetailInsert", queryString);


            queryString = " @GCGBCommandID int, @SerialID int, @GCGBCommandCollectionSerialID int, @GCGBCommandDate datetime, @MaterialSetID int, @Quantity float, @QuantityOutput float, @MaterialSemiID int, @QuantityPerSet float, @QuantitySemi float, @Remarks nvarchar(150), @Approved bit, @InActive bit " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       UPDATE      GCGBCommandDetail SET GCGBCommandCollectionSerialID = @GCGBCommandCollectionSerialID, GCGBCommandDate = @GCGBCommandDate, MaterialSetID = @MaterialSetID, Quantity = @Quantity, QuantityOutput = @QuantityOutput, MaterialSemiID = @MaterialSemiID, QuantityPerSet = @QuantityPerSet, QuantitySemi = @QuantitySemi, Remarks = @Remarks, Approved = @Approved, InActive = @InActive WHERE GCGBCommandID = @GCGBCommandID AND SerialID = @SerialID " + "\r\n";
            queryString = queryString + "       SELECT      GCGBCommandID, SerialID, GCGBCommandCollectionSerialID, GCGBCommandDate, MaterialSetID, Quantity, QuantityOutput, MaterialSemiID, QuantityPerSet, QuantitySemi, Remarks, Approved, InActive FROM GCGBCommandDetail WHERE GCGBCommandID = @GCGBCommandID AND SerialID = @SerialID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("GCGBCommandDetailUpdate", queryString);


            queryString = " @GCGBCommandID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       DELETE FROM GCGBCommandDetail WHERE GCGBCommandID = @GCGBCommandID " + "\r\n";
            SQLDatabase.CreateStoredProcedure("GCGBCommandDetailDelete", queryString);





            /// <summary>
            /// Check for editable
            /// </summary>
            queryArray = new string[0];
            //queryArray[0] = " SELECT TOP 1 MarketingIncentiveID FROM MarketingIncentiveMaster WHERE MarketingIncentiveID = @FindIdentityID AND Approved = 1 ";

            SQLDatabase.CreateProcedureToCheckExisting("GCGBCommandApproved", queryArray);



            /// <summary>
            /// Check for editable
            /// </summary>
            queryArray = new string[0];
            //queryArray[0] = " SELECT TOP 1 MarketingIncentiveID FROM MarketingPaymentMaster WHERE MarketingIncentiveID = @FindIdentityID ";

            SQLDatabase.CreateProcedureToCheckExisting("GCGBCommandEditable", queryArray);

        }



    }
}
